#CODE DESCRIPTION: Assigns water quality data to production wells from each quarter from 2005-2017

#STEPS IN THIS CODE
#1. Merge water quality data from a given quarter .csv file to monitoring wells .shp file
#2. Interpolate water quality field into a raster using natural neighbor or spline interpolation technique
#3. Extract points from raster (extract values to points)
#4. Export to Excel to merge all together in Stata 
#5. Repeat for all water quality measures for all years--Loop

#import modules
import arcpy
import os

arcpy.CheckOutExtension("Spatial")
arcpy.CheckOutExtension("3D")
from arcpy.sa import *

#allows overwriting in geodatabase
arcpy.env.overwriteOutput = True

#set local variables
production_wells =  r'D:\Ellen\Dropbox\Pajaro_AgInnovation\Data\Raw_Data\PV_Water_Bruno_E_20210427_Data_Request.gdb\PV_Water_Metered_Wells'
monitoring_wells = r'D:\Ellen\Dropbox\Pajaro_AgInnovation\Data\Raw_Data\PV_Water_20181105_Data_Request\PV_Water_Groundwater_Monitoring_Network_Wells.shp'

date = "20220729"

#######want same cell size consistent with DEM and groundwater contour detail in those rasters

#bad_list holds all quarters that have insufficient data to do the spatial interpolation. 

bad_list = ["2001:1", "2001:3", \
            "2002:1", \
            "2005:1", \
            "2006:1", \
            "2008:1", "2008:3", \
            "2009:2", \
            "2020:1", "2020:3" ]
print bad_list


#ADD JOIN is not working for 2001 Q3 and 2009 Q2 because not entering as strings so the match isn't working, but the function doesn't actually fail

for y in range(2001, 2021):
    for q in range(1,5):

        if str(y) + ":" + str(q) not in bad_list:

            print "CL_" + str(y) + "_Q" + str(q) + "_" + date+ ".csv"

            arcpy.MakeFeatureLayer_management(monitoring_wells, "monitoring_layer")


            arcpy.AddJoin_management("monitoring_layer",
                         "Site_Name", "D:/Ellen/Dropbox/Pajaro_AgInnovation/Data/CL_tables/Replicate/CL_" + str(y) + "_Q" +str(q) + "_" + date+ ".csv",
                         "sitename", "KEEP_ALL")


            arcpy.gp.NaturalNeighbor_sa("monitoring_layer",
                            "CL_" + str(y) + "_Q" +str(q) + "_" + date + ".csv.chloride",
                            "D:/Ellen/Dropbox/Pajaro_AgInnovation/Data/WorkingData.gdb/CL_" + str(y) + "_Q" + str(q) + "_nn_" + date,
                            "270.436831828654")

            arcpy.gp.ExtractValuesToPoints_sa(production_wells,
                                  "D:/Ellen/Dropbox/Pajaro_AgInnovation/Data/WorkingData.gdb/CL_" + str(y) + "_Q" + str(q) + "_nn_" + date,
                                  "D:/Ellen/Dropbox/Pajaro_AgInnovation/Data/WorkingData.gdb/CL_to_wells_" + str(y) + "_Q" + str(q) + "_" + date,
                                  "INTERPOLATE", "VALUE_ONLY")

            arcpy.TableToExcel_conversion("D:/Ellen/Dropbox/Pajaro_AgInnovation/Data/WorkingData.gdb/CL_to_wells_" + str(y) + "_Q" + str(q)+ "_" + date,
                              "D:/Ellen/Dropbox/Pajaro_AgInnovation/Data/CL_tables/Replicate/CL_to_wells_" + str(y) + "_Q" + str(q) + "_" + date + ".xls",
                              "NAME", "CODE")
